// Copyright (c) 2009-2013 Pica8, Inc.
// http://pica8.com
// Author: Henry Fung

#include "platforms/statdb_tb_def.hh"
#include "platforms/pica_statdb_tb_impl.hh"

#ifdef __cplusplus
extern "C" {
#endif
#include <stdio.h>
#include <stdlib.h>
#include <errno.h>
#include <time.h>
#include <sys/time.h>
#include <stdint.h>
#include <string.h>
#include <assert.h>
#include <sqlite3.h>
#include <ctype_pica_lcmgr_api.h>
#ifdef __cplusplus
}
#endif

using namespace std;


#define RFC_TIME_STR_LEN 40
#define MAX_SQL_LEN 1000
#define MAX_SQL_TABLES 40
#define NUM_DEL_SQL_TABLES 4
#define MAX_FULL_TABLE_NAME_LEN MAX_TABLE_NAME_LEN+20
#define TMP_STATDB_FILE "/tmp/statdb.dat"

#define count_columns(ARRAY) (sizeof(ARRAY)/sizeof(*ARRAY))


struct timeval lastStartTimestamp;
bool pica_stat_sync_requested = false;
bool pica_stat_write_file_flag = false;

statdb_tb_info_t *p_tb_info[STATDB_TB_MAX];
statdb_tb_column_t *p_tb_column[STATDB_TB_MAX];

static FILE* output = stdout; /* either stdout or stderr */

sqlite3 *statdb;
//these are for table "tables"
static sqlite3_stmt *createTablesTblStmt = 0;
static sqlite3_stmt *insertTablesTblStmt = 0;
static sqlite3_stmt *findOfFlowTblStmt = 0;
static sqlite3_stmt *findOldestTablesStmt = 0;
static sqlite3_stmt *deleteOldestTablesStmt = 0;
static int num_sql_tables = 0;

//static sqlite3_stmt *insertPortTblStmt = 0;
//static sqlite3_stmt *insertOfFlowTblStmt = 0;
//init immediately after the table created.
static sqlite3_stmt *statdb_tb_insert_stmt[STATDB_TB_MAX]; 

static statdb_tb_column_t tablesTbl[] =
{
    {"tableName", 1, "TEXT PRIMARY KEY", NULL},
    {"tableType", 2, "TEXT", NULL},
    {"time", 3, "TEXT", NULL}
};

bool
pica_db_prepare_create_sql(char *sql, uint32_t len,
        char *table, statdb_tb_column_t schema[], uint32_t cols)
{
    uint32_t s;

    snprintf(sql, len, "CREATE TABLE %s (", table);
    for (s = 0; s < cols; s++) {
         /* note: this is ok as schema[] is not sorted by name yet */
         if (s > 0) {
             strncat(sql, ",", len);
         }
         strncat(sql, schema[s].name, len);
         strncat(sql, " ", len);
         strncat(sql, schema[s].type, len);
    }
    strncat(sql, ")", len);
    return true;
}
#if 0
bool
pica_db_prepare_create_stmt(sqlite3_stmt **pstmt, char *table, statdb_tb_column_t schema[], uint32_t cols)
{
    int rc;
    char sql[MAX_SQL_LEN];
    const char *tail;

    rc = pica_db_prepare_create_sql(sql, MAX_SQL_LEN, table, schema, cols);

    /* note: this could fail if the database already exists */
    rc = sqlite3_prepare_v2(statdb, sql, strlen(sql)+1, pstmt, &tail);
    if (rc) {
        fprintf(output, "Can't prepare create %s table\n", table);
        return false;
    }
    return true;
}

static bool
column_name_cat(char *clmstr, uint32_t clmlen, 
        statdb_tb_column_t schema[], uint32_t cols)
{
    uint32_t s;
    int rc;
    uint32_t len = clmlen;

    snprintf(clmstr, len, "INSERT INTO %s VALUES (", table);
    for (s = 0; s < cols; s++) {
        /* note: this is ok as schema[] is not sorted by name yet */
        if (s > 0)
            strncat(sql, ",:", len);
        else
            strncat(sql, ":", len);
        strncat(sql, schema[s].name, len);
    }
    strncat(sql, ")", len);

    return true;
}
bool
statdb_tb_insert_stmt_init()
{
    uint32_t tb_index;
    statdb_tb_info_t *ptb;
    statdb_tb_column_t *pclm;
    char sql[MAX_SQL_LEN];
    char clmstr[MAX_SQL_LEN];
    uint32_t len = MAX_SQL_LEN;

    for(tb_index=0; tb_index<STATDB_TB_MAX; tb_index++) {
        ptb = p_tb_info[tb_index];
        pclm = p_tb_column[tb_index];

        if(ptb->tb_column_str) {
            memcpy(clmstr, ptb->tb_column_str,strlen(ptb->tb_column_str)+1);
        } else {
            column_name_cat(clmstr, len, pclm, ptb->column_num);
        }

        snprintf(clmstr, len, "INSERT INTO %s VALUES (%s)", ptb->);
        rc = sqlite3_prepare_v2(statdb, sql, strlen(sql)+1, pstmt, &tail);
        if (rc) {
            fprintf(output, "Can't prepare insert %s table\n", table);
            return false;
        }
    }

}
#endif

bool
pica_db_prepare_insert_stmt(sqlite3_stmt **pstmt, 
        char *table, statdb_tb_column_t schema[], uint32_t cols)
{
    uint32_t s;
    int rc;
    char sql[MAX_SQL_LEN];
    uint32_t len = MAX_SQL_LEN;
    const char *tail;

    snprintf(sql, len, "INSERT INTO %s VALUES (", table);
        for (s = 0; s < cols; s++) {
         /* note: this is ok as schema[] is not sorted by name yet */
         if (s > 0)
             strncat(sql, ",:", len);
         else
             strncat(sql, ":", len);
         strncat(sql, schema[s].name, len);
    }
    strncat(sql, ")", len);

    rc = sqlite3_prepare_v2(statdb, sql, strlen(sql)+1, pstmt, &tail);
    if (rc) {
        fprintf(output, "Can't prepare insert %s table\n", table);
        return false;
    }

    return true;
}

bool
statdb_tb_fill_column_value(sqlite3_stmt *pstmt,
        statdb_tb_info_t *ptb, statdb_tb_column_t *pclm)
{
    char cvalue[200] = {'\0'};
    statdb_tb_column_t *tmp;
    uint32_t id;
    int rc;
    bool isvalid;

    /* insert all rows */
    /*before while, must rest table column index, create a new func.*/
    ptb->rest_tb_index();
    while(ptb->get_tb_next_row()) {
        
        for(id=0,tmp = pclm; id<ptb->column_num; id++,tmp++) {
            isvalid = tmp->get_column_value(cvalue);
            if (!isvalid) 
                continue; //don't bind value when it is not valid.
            rc = sqlite3_bind_text(pstmt, tmp->id, cvalue, strlen(cvalue), SQLITE_TRANSIENT);
            if (rc!=SQLITE_OK) {
                fprintf(output,"%s:%d: rc=%d,column:%s, id:%d, value:%s\n",
                        __FILE__,__LINE__,
                        rc, tmp->name,tmp->id, cvalue);
                return false;
            }
        }
        
        rc = sqlite3_step(pstmt);
        //if (rc!=SQLITE_DONE) {
        //    //SQLITE_ROW         100  /* sqlite3_step() has another row ready
        //    //fprintf("",rc, pclm->name,pclm->id, cvalue);
        //    return false;
        //}
        //sqlite3_clear_bindings();
        //if (rc!=SQLITE_OK) {
        //    //fprintf("",rc, pclm->name,pclm->id, cvalue);
        //    return false;
        //}
        sqlite3_clear_bindings(pstmt);
        sqlite3_reset(pstmt);
    }
    return true;
}

extern bool pica_is_numeric(const char *p);
bool
write_column_value_to_file(FILE *fp,
        statdb_tb_info_t *ptb, statdb_tb_column_t *pclm)
{
    char cvalue[200] = {'\0'};
    statdb_tb_column_t *tmp;
    uint32_t id;
    int rc;
    bool isvalid;
    uint64_t val64;

    /* insert all rows */
    /*before while, must rest table column index, create a new func.*/
    ptb->rest_tb_index();

    while(ptb->get_tb_next_row()) {
        
        for(id=0,tmp = pclm; id<ptb->column_num; id++,tmp++) {
            isvalid = tmp->get_column_value(cvalue);
            //FIXME: If isvalid is not true, we need to know the value type,and write
            //related bytes zero data to file. we should add data type identify latter.
            if (!isvalid) 
                continue; 
            if(pica_is_numeric(cvalue)) {
                if (tmp->name == "flowId" ||
                        tmp->name == "queue")
                    continue;
                val64 = atoll(cvalue);
                rc = fwrite(&val64, 8, 1,fp);
                if(rc != 1) {
                    fprintf(output, "fwrite error:%d column_name:%s, column_value:%s\n",
                            rc, tmp->name, cvalue);
                    return false;
                }
            //} else {
            //    rc = fwrite(cvalue, strlen(cvalue)+1, 1, fp);
            //    if(rc != 1) {
            //        fprintf(output, "fwrite error:%d column_name:%s,column_value:%s\n",
            //                rc, tmp->name, cvalue);
            //        return false;
            //    }
            }
        }
    }
    return true;
}

bool
pica_db_prepare_find_oldest_stmt(sqlite3_stmt **pstmt, char *table, statdb_tb_column_t schema[])
{
    int rc;
    char sql[MAX_SQL_LEN];
    uint32_t len = MAX_SQL_LEN;
    const char *tail;

    snprintf(sql, len, "SELECT %s FROM %s ORDER BY %s ASC LIMIT %d",
             schema[0].name, table, schema[2].name, NUM_DEL_SQL_TABLES);
    rc = sqlite3_prepare_v2(statdb, sql, strlen(sql)+1, pstmt, &tail);
    if (rc) {
        fprintf(output, "Can't prepare find oldest tables\n");
        return false;
    }

    return true;
}

bool
pica_db_prepare_delete_oldest_stmt(sqlite3_stmt **pstmt, char *table, statdb_tb_column_t schema[])
{
    int rc;
    char sql[MAX_SQL_LEN];
    uint32_t len = MAX_SQL_LEN;
    const char *tail;

    snprintf(sql, len, "DELETE FROM %s WHERE %s IN (\
        SELECT %s FROM %s ORDER BY %s ASC LIMIT %d)",
            table, schema[0].name, schema[0].name, table, schema[2].name,
            NUM_DEL_SQL_TABLES);
    rc = sqlite3_prepare_v2(statdb, sql, strlen(sql)+1, pstmt, &tail);
    if (rc) {
        fprintf(output, "Can't prepare delete oldest tables\n");
        return false;
    }

    return true;
}

bool
pica_db_prepare_find_stmt(sqlite3_stmt **pstmt, char *table, statdb_tb_column_t schema[],
                          int tgt_col, int match_col) 
{
    int rc;
    char sql[MAX_SQL_LEN];
    uint32_t len = MAX_SQL_LEN;
    const char *tail;

    snprintf(sql, len, "SELECT %s FROM %s WHERE %s=:%s",
             schema[tgt_col].name, table,
             schema[match_col].name, schema[match_col].name);
    rc = sqlite3_prepare_v2(statdb, sql, strlen(sql)+1, pstmt, &tail);
    if (rc) {
        fprintf(output, "Can't prepare find %s stmt\n", table);
        return false;
    }
    return true;
}

static void
pica_db_flush()
{
    int rc = SQLITE_ROW;
    char sql[MAX_SQL_LEN];
    const unsigned char *table_name;
    char names[NUM_DEL_SQL_TABLES][MAX_FULL_TABLE_NAME_LEN];
    int i = 0;

    if (num_sql_tables < MAX_SQL_TABLES)
        return;

     /* store all table names first and then delete the tables, as
        DROP table while SELECT is pending causes SQLITE_LOCK error.
      */
    while ((rc = sqlite3_step(findOldestTablesStmt)) == SQLITE_ROW) {
        if (i >= NUM_DEL_SQL_TABLES)
            break;
        table_name = sqlite3_column_text(findOldestTablesStmt, 0);
        strncpy(names[i++], (const char *)table_name, MAX_FULL_TABLE_NAME_LEN);
    }
    sqlite3_reset(findOldestTablesStmt);
    if (rc != SQLITE_DONE)
        return; //without modifying 'tables' table
    while (i-- > 0) {
        snprintf(sql, MAX_SQL_LEN, "DROP TABLE IF EXISTS %s", names[i]);
        rc = sqlite3_exec(statdb, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
            return;
    }

    /* remove entries from 'tables' table */
    rc = sqlite3_step(deleteOldestTablesStmt);
    if (rc == SQLITE_DONE) {
        num_sql_tables -= NUM_DEL_SQL_TABLES;
        if (num_sql_tables < 0)
            num_sql_tables = 0;
    }
    rc = sqlite3_reset(deleteOldestTablesStmt);

    return;
}

bool
pica_db_create_table_all(struct timeval timestamp)
{
    uint32_t tb_index;
    statdb_tb_info_t *ptb;
    statdb_tb_column_t *pclm;
    struct tm *t;
    char tableName[MAX_FULL_TABLE_NAME_LEN];
    char rfcTime[RFC_TIME_STR_LEN];
    uint64_t value;
    uint32_t id;
    char sql[MAX_SQL_LEN];
    FILE *fp = NULL;
    int rc;

    t = gmtime(&timestamp.tv_sec);
    snprintf(rfcTime, RFC_TIME_STR_LEN, "%04d-%02d-%02dT%02d:%02d:%02d.%06dZ",
             t->tm_year+1900,  t->tm_mon+1, t->tm_mday, t->tm_hour,
             t->tm_min, t->tm_sec, (int)(timestamp.tv_usec));

    /*write counters value to file if FLAG being set*/
    if(pica_stat_write_file_flag) {

        fp = fopen(TMP_STATDB_FILE,"w+");
        if(fp == NULL) {
            fprintf(output, "fopen /tmp/statdb.dat error, with errno:%d\n",errno);
            return false;
        }
    }

    /*create every table in for loop.*/
    for(tb_index=0; tb_index<STATDB_TB_MAX; tb_index++) {
        ptb = p_tb_info[tb_index];
        pclm = p_tb_column[tb_index];

        //ASSERT_STATDB_TB_INFO_VALID(ptb);

        /*write value to tmp file.*/
        if(fp != NULL) {
            write_column_value_to_file(fp, ptb, pclm);
        }

        /*write value to sqlite DB statdb.*/
        tableName[0] = '\0';
        sql[0] = '\0';
        snprintf(tableName, MAX_FULL_TABLE_NAME_LEN, "%s_%04d%02d%02d%02d%02d%02d",
                ptb->tb_pfx_name ,t->tm_year+1900, t->tm_mon+1, t->tm_mday,
                t->tm_hour, t->tm_min, t->tm_sec);

        rc = pica_db_prepare_create_sql(sql, MAX_SQL_LEN, tableName, pclm, ptb->column_num);
        rc = sqlite3_exec(statdb, sql, 0, 0, 0);
        if (rc != SQLITE_OK) {
            fprintf(output, "Can't create '%s' table\n", tableName);
            return false;
        }

        /* prepare for the database insert statement */
        rc = pica_db_prepare_insert_stmt(&statdb_tb_insert_stmt[tb_index],
                tableName, pclm, ptb->column_num);
        if (!rc) { //if failed to prepare insert stmt, it can not insert value.
            //drop table tablename
            continue; //continue to create next table instead of return.
        }

        rc = statdb_tb_fill_column_value(statdb_tb_insert_stmt[tb_index], ptb, pclm);
        if (!rc) {
            //drop table tablename
            continue;
        }
        /* release table insert statement */
        rc = sqlite3_finalize(statdb_tb_insert_stmt[tb_index]);
        statdb_tb_insert_stmt[tb_index] = 0;

        /* update 'table of tables' */
        rc = sqlite3_bind_text(insertTablesTblStmt, 1, tableName, strlen(tableName), 0);
        rc = sqlite3_bind_text(insertTablesTblStmt, 2, ptb->tb_pfx_name, strlen(ptb->tb_pfx_name), 0);
        rc = sqlite3_bind_text(insertTablesTblStmt, 3, rfcTime, strlen(rfcTime), 0);
        rc = sqlite3_step(insertTablesTblStmt);
        rc = sqlite3_reset(insertTablesTblStmt);

        num_sql_tables++;

        pica_db_flush();
    }
    
    if(fp != NULL)
        fclose(fp);

    return rc;
}

bool
pica_db_execute_sql(const char *sql, int (*callback)(void*,int,char**,char**),
                    void *arg, char**errmsg)
{
    int rc;

    rc = sqlite3_exec(statdb, sql, callback, arg, errmsg);
    if (rc != SQLITE_OK)
        return false;
    return true;
}
static void
pica_db_stat_notify_cb(uint32_t timestamp_sec, uint32_t timestamp_usec)
{
    struct timeval start_timestamp;

    if (!pica_stat_sync_requested)
        return;
    start_timestamp.tv_sec = (time_t)timestamp_sec;
    start_timestamp.tv_usec = (suseconds_t)timestamp_usec;
    pica_db_create_table_all(start_timestamp);
    lastStartTimestamp = start_timestamp;
}

static void statdb_tb_init()
{
    PICA_PORTS_TB_INFO_INIT
    PICA_OFFLOW_TB_INFO_INIT
    PICA_PORTS_TB_ITEM_INIT
    PICA_OFFLOW_TB_ITEM_INIT
}
bool
pica_db_init()
{
    int rc;
    char tableName[20];
    char sql[MAX_SQL_LEN];

    //rc = sqlite3_open("pica.db", &statdb);
    rc = sqlite3_open(":memory:", &statdb);
    if (rc) {
        fprintf(output, "Can't open database: %s\n", sqlite3_errmsg(statdb));
        sqlite3_close(statdb);
        return false;
    }

    sprintf(tableName, "tables");
    rc = pica_db_prepare_create_sql(sql, MAX_SQL_LEN, tableName, tablesTbl, count_columns(tablesTbl));
    rc = sqlite3_exec(statdb, sql, 0, 0, 0);
    if (rc != SQLITE_OK) {
        fprintf(output, "Can't create '%s' table\n", tableName);
        sqlite3_close(statdb);
        return false;
    }
    rc = pica_db_prepare_insert_stmt(&insertTablesTblStmt, tableName, tablesTbl, count_columns(tablesTbl));
    rc = pica_db_prepare_find_oldest_stmt(&findOldestTablesStmt, tableName, tablesTbl);
    rc = pica_db_prepare_delete_oldest_stmt(&deleteOldestTablesStmt, tableName, tablesTbl);


#if 0 // when proven necessary
    /* clear all counters */
    for (port = 1; port <= pica_max_line_card_user_port; port++) {
        usp_t usp(1, 1, port);
        rc = pica_clear_port_stat_counter(usp);
    }
#endif
    
    statdb_tb_init();
    rc = ctype_pica_register_stat_notify_callback(pica_db_stat_notify_cb, NULL);

    return rc;
}
bool
pica_db_free(void *ptr)
{
    sqlite3_free(ptr);
    return true;
}
